/*
Given the document key, returns everything from DocumentMain except the blob (for security and performance reasons)
plus DocumentTypeName, DocumentTypeDesc, and DocumentIconURL from the associated DocumentTypeRef record.

NOTE: Security is bypassed since the call requires the key (e.g., a reference from an object to which the user
has access) and the sensitive part, the blob, is not returned.
*/
CREATE PROC [dbo].[asi_DocumentMainByKey]
   @key uniqueidentifier
AS
BEGIN
   DECLARE @documentKey uniqueidentifier

   EXEC asi_DocumentGetLatestKey @key, @documentKey OUT

   SELECT a.DocumentKey,
          a.DocumentVersionKey,
          a.DocumentStatusCode,
          a.DocumentName,
          a.AlternateName,
          a.DocumentDescription,
          a.DocumentTypeCode,
          a.IsSystem,
          a.ContainsChildrenFlag,
          a.RelatedDocumentVersionKey,
          a.AccessKey,
          a.DefaultChildAccessKey,
          a.StatusUpdatedOn,
          a.StatusUpdatedByUserKey,
          a.CreatedOn,
          a.CreatedByUserKey,
          a.UpdatedOn,
          a.UpdatedByUserKey,
          b.DocumentTypeName,
          b.DocumentTypeDesc,
          b.DocumentIconURL,
          c.DocumentKey AS RelatedDocumentKey,
          c.DocumentTypeCode AS RelatedDocumentTypeCode,
          c.DocumentStatusCode AS RelatedDocumentStatusCode,
          c.DocumentName AS RelatedDocumentName,
          c.AlternateName AS RelatedAlternateName,
          c.DocumentDescription AS RelatedDocumentDescription,
          c.CreatedOn AS RelatedCreatedOn,
          c.CreatedByUserKey AS RelatedCreatedByUserKey,
          c.UpdatedOn AS RelatedUpdatedOn,
          c.UpdatedByUserKey AS RelatedUpdatedByUserKey,
          d.DocumentTypeName AS RelatedDocumentTypeName,
          d.DocumentTypeDesc AS RelatedDocumentTypeDesc,
          d.DocumentIconURL AS RelatedDocumentIconURL,
          d.ShortcutIconURL AS RelatedShortcutIconURL
     FROM DocumentMain a INNER JOIN DocumentTypeRef b ON a.DocumentTypeCode = b.DocumentTypeCode
          LEFT OUTER JOIN DocumentMain c ON a.RelatedDocumentVersionKey = c.DocumentVersionKey AND c.DocumentStatusCode IN (40,60)
          LEFT OUTER JOIN DocumentTypeRef d ON c.DocumentTypeCode = d.DocumentTypeCode
    WHERE a.DocumentKey = @documentKey
END

GO